package com.jiepos.mpos.core.util;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * 
 * <p>标题: —— excel操作工具类</p>
 * <p>模块: 通用平台</p>
 * <p>版权: Copyright (c) 2017 江苏杰博实信息技术有限公司
 * @author 娄伟峰
 * @version 1.0
 * @since 2017年4月20日 上午10:36:44
 * @history（历次修订内容、修订人、修订时间等）
 */
public class ExcelUtil {
    /** 子类的日志 */
    public static  final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
	/**
	 * 写excel文件
	 * @param fileName
	 * @param sheetName
	 * @param excelHead
	 * @param dataList
	 */
	public static String writeExcel(String fileName,String sheetName,List<String> excelHead,List<Map> dataList) {
		WritableWorkbook wb = null;
		WritableSheet ws = null;
		
		log.debug("excel总行数==========="+dataList.size());
		if(dataList != null && dataList.size() > 50000){
			return "导出记录最多不能超过5万条";
		}
		try {
			// 创建可写入的 Excel工作薄
			wb = Workbook.createWorkbook(new File(fileName));
			// 创建Excel工作表
			ws = wb.createSheet(sheetName, 0);
			//拼装excel头部
			Label label1 = null;			
			for(int i = 0 ; i < excelHead.size() ; i++){
				// 添加Label对象
				label1 = new Label(i, 0, excelHead.get(i));
				ws.addCell(label1);
				  
			}
			
			//写入excel数据
			Collection col = null;
			int i = 0;
			Iterator iter = dataList.iterator();
			Label labelCF = null;
			Iterator colIter = null;
			for(int j = 0 ; j < dataList.size() ; j++){			 
				col = (dataList.get(j)).values();
				colIter = col.iterator();
				i = 0 ;
				while(colIter.hasNext() ){	
					if(i != 0){
						WritableCellFormat wcfF = new WritableCellFormat(NumberFormats.TEXT);
						labelCF = new Label( i-1,j+1, StringUtils.objToString(colIter.next()),wcfF);
						ws.addCell(labelCF);
					}else{
						colIter.next();
					}					
					i++;				
				}
			}
			
			wb.write(); // 写入Exel工作表
		//	wb.close(); // 关闭Excel工作薄对象
			log.debug("excel写入完成");
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}finally{
			if(wb!=null){
				try {
					
					wb.close();
					log.debug("关闭WritableWorkbook");
				} catch (WriteException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	/**
	 * 生成excel
	 * @param filePath 文件路径
	 * @param sheetName sheet页名称
	 * @param dataList 数据集合
	 * @param excelHead 列头
	 * @return
	 */
	public static File writeExcel(String filePath, String sheetName, List<Map<String, Object>> dataList, String...excelHead) {
		if(StringUtils.isBlank(filePath)){
			return null;
		}
		File file = new File(filePath);
		WritableWorkbook wb = null;
		WritableSheet ws = null;
		try {
			wb = Workbook.createWorkbook(file);
			ws = wb.createSheet(sheetName, 0);
			Label label1 = null;
			if(excelHead != null && excelHead.length > 0){
				for(int i=0; i<excelHead.length; i++){
					label1 = new Label(i, 0, excelHead[i]);
					ws.addCell(label1);
				}
			}
			//写入excel数据
			Collection col = null;
			Iterator iter = dataList.iterator();
			Label labelCF = null;
			Iterator colIter = null;
			for(int j = 0 ; j < dataList.size() ; j++){
				col = (dataList.get(j)).values();
				colIter = col.iterator();
				int i = 0;
				while(colIter.hasNext()){
					WritableCellFormat wcfF = new WritableCellFormat(NumberFormats.TEXT);
					labelCF = new Label(i++, j, StringUtils.objToString(colIter.next()), wcfF);
					ws.addCell(labelCF);
				}
			}
			wb.write(); // 写入Exel工作表
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}finally{
			if(wb!=null){
				try {
					wb.close();
				} catch (WriteException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return file;
	}
	
	/**
	 * 读取excel
	 * @param excelFile excel文件
	 */
	public static List<Map> readExcel(File excelFile){
		Workbook book = null;
		List<Map> list = new ArrayList<Map>();
		 try {
			// 打开文件
			book = Workbook.getWorkbook(excelFile);
			// 获得第一个表的工作对象，“0”表示第一个表			
			Sheet sheet = book.getSheet(0);
		
			for (int i = 0; i < sheet.getRows(); i++) {
				// 获得行
				Cell[] row = sheet.getRow(i);
				Map<String, String> rowMap = new HashMap<String, String>();
				
				for (int j = 0; j < row.length; j++) {
					// 获得单元格内容
					String content = row[j].getContents();

					// 因为从0开始,所以要+1
					rowMap.put(String.valueOf(j + 1), StringUtils.trim(content));
				}
				if (!rowMap.isEmpty()) {
					list.add(rowMap);
				}
				log.debug("已经处理过"+i+"行");
			}   

			book.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		}finally{  
            if(book!=null){  
                book.close();  
            }  
        }  
		return list;
	}
}
